package com.foreverwin.jzymes.production.cutter.service.impl;



import com.foreverwin.jzymes.framework.exception.BusinessException;
import com.foreverwin.jzymes.framework.util.FTPUtil;
import com.foreverwin.jzymes.production.cutter.service.IExportExcelService;
import com.foreverwin.jzymes.production.cutter.service.IToolBreakAdiviceService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExportExcelServiceImpl implements IExportExcelService {

    @Autowired
    IToolBreakAdiviceService toolBreakAdiviceService;
    @Autowired
    FTPUtil ftpUtil;

    /**
     * 将一个类的所有声明属性和值存入map（属性值为int型时，0时不加入，
     * 属性值为String型或Long时为null和“”不加入）
     * @param obj
     * @return 属性名和值对应的map
     */
    public  Map<String, Object> setConditionMap(Object obj){
        Map<String, Object> map = new HashMap<String, Object>();
        if(obj==null){
            return null;
        }
        try {
            //获取该类的所有声明的属性的名字
            Field[] fields = obj.getClass().getDeclaredFields();
            //循环遍历以属性名作为key以boject的get方法获取的值作为value存入map
            for(Field field : fields){
                String fieldName =  field.getName();
                if(getValueByFieldName(fieldName,obj)!=null)
                    map.put(fieldName,  getValueByFieldName(fieldName,obj));
            }
        } catch (Exception e) {

            e.printStackTrace();
        }
        return map;
    }
    /**
     * 根据属性名获取该类此属性的值
     * @param fieldName
     * @param object
     * @return get方法的返回值
     */
    private static Object getValueByFieldName(String fieldName,Object object){
        //拼接get方法
        String firstLetter=fieldName.substring(0,1).toUpperCase();
        String getter = "get"+firstLetter+fieldName.substring(1);
        try {
            //从实体类中获取该get方法
            Method method = object.getClass().getMethod(getter, new Class[]{});
            //利用该实体类对象执行该get方法
            Object value = method.invoke(object, new Object[] {});
            //如果是时间格式化
            if(value.getClass().equals(Date.class)){
                Date date =  new Date(((Date)value).getTime());
                SimpleDateFormat time=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                return time.format(date);
            }
            return value;
        } catch (Exception e) {
            return null;
        }
    }

    public  File reportExport(List<?> list,String sheetName){
        int size = list.size();
        //获取一个对象为填入表头提供信息
        Object object = list.get(0);
        //获取该类的所有声明即表头
        Field[] fields = object.getClass().getDeclaredFields();
        //每一行的列数
        int columnSize = fields.length ;
        //在Excel工作簿中建一工作表，其名为缺省值。
        HSSFWorkbook workbook = new HSSFWorkbook();
        //如要新建一名为sheetName[0]的工作表
        HSSFSheet sheet = workbook.createSheet( sheetName );
        //在索引的位置创建行（根据记录数添加）
        HSSFRow[] row = new HSSFRow[size];
        //每一行创建列（根据树形字段）
        HSSFCell[] cell = new HSSFCell[columnSize];
        //单元格式
        CellStyle cellStyle= workbook.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);

        //字体
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        cellStyle.setFont(font);
        //存储列字段值最大值
        Map<String,Short> mapMax = new HashMap();
        //写入表头
        row[0] = sheet.createRow(0);
        for(int i=1;i<columnSize;i++){
            mapMax.put(fields[i].getName(), (short)3000);
            cell[i] = row[0].createCell((short)i);
            //cell[i].setCellType(HSSFCell.CELL_TYPE_STRING);
            cell[i].setCellStyle(cellStyle);
            cell[i].setCellValue(fields[i].getName());
        }
        //写入具体数据
        for(int j=0;j<size;j++){
            //根据类获得的属性名值相对的map
            Map<?, ?> map = setConditionMap(list.get(j));
            row[j] = sheet.createRow(j+1);
            for(int i=0;i<columnSize;i++){
                cell[i] = row[j].createCell((short)i);
                //cell[i].setCellType(HSSFCell.CELL_TYPE_STRING);
                if(map.get(fields[i].getName())==null){
                    cell[i].setCellValue("");
                } else {
                    cell[i].setCellValue(map.get(fields[i].getName()).toString());
                    //比较最大值并且更新
                    if(mapMax.get(fields[i].getName())>map.get(fields[i].getName()).toString().length())
                        mapMax.put(fields[i].getName(), (short)(map.get(fields[i].getName()).toString().length()*280+1000));
                }
            }
        }
        //设置列宽度
       /* for(int i=0;i<fields.length;i++){
            sheet.setColumnWidth((short)i, (short)mapMax.get(fields[i].getName()));
        }*/
        try {
            String downloadDir = ftpUtil.getDownloadDir();
            File file = new File(downloadDir + "ExCel.xlsx");
            if(!file.exists())
                file.createNewFile();
            FileOutputStream outputStream = new FileOutputStream( file );
            workbook.write(outputStream);
            outputStream.flush();
            //操作结束，关闭文件
            outputStream.close();
            workbook.close();

            return  file;
        } catch (IOException e) {
           throw BusinessException.build( e.getMessage() );
            //e.printStackTrace();
        }
    }

    /**
     * 导出数据库表未有字段
     * @param data
     * @param title
     * @param rowName
     * @param excel
     * @return
     */
    @Override
    public File Export(List<Object[]> data, String title, String[] rowName, String excel) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet( title );
        //产生标题
        HSSFRow rowm = sheet.createRow(0 );
        HSSFCell cellTittle = rowm.createCell(0);

        rowm.setHeight( (short)( 25*35 ) ); //设置高度

        HSSFCellStyle columnTopStyle = this.getColumnTopStyle( workbook ); //获取列头样式对象
        HSSFCellStyle style = this.getStyle(workbook);   //单元格样式对象

        sheet.addMergedRegion( new CellRangeAddress( 0,0,0,(rowName.length -1 ) ));
        cellTittle.setCellStyle( columnTopStyle );
        cellTittle.setCellValue( title );

        int columnNum = rowName.length;
        HSSFRow rowRowName = sheet.createRow( 1 );

        rowRowName.setHeight( (short) (25*35) );

        // 将列头设置到sheet的单元格中
        for(int n=0;n<columnNum;n++){
            HSSFCell  cellRowName = rowRowName.createCell(n);                //创建列头对应个数的单元格
            cellRowName.setCellType( HSSFCell.CELL_TYPE_STRING );               //设置列头单元格的数据类型
            HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
            cellRowName.setCellValue(text);                                    //设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle);                        //设置列头单元格样式
        }

        //将查询出的数据设置到sheet对应的单元格中
        for(int i=0;i<data.size();i++){

            Object[] obj = data.get(i);//遍历每个对象
            HSSFRow row = sheet.createRow(i+2);//创建所需的行数

            row.setHeight((short) (25 * 20)); //设置高度

            for(int j=0; j<obj.length; j++){
                HSSFCell  cell = null;   //设置单元格的数据类型
                if(j == 0){
                    cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(i+1);
                }else{
                    cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                    if(!"".equals(obj[j]) && obj[j] != null){
                        cell.setCellValue(obj[j].toString());                        //设置单元格的值
                    }else {
                        cell.setCellValue("");
                    }
                }
                cell.setCellStyle(style);                                    //设置单元格样式
            }
        }

        try {
            String downloadDir = ftpUtil.getDownloadDir();
            File file = new File(downloadDir + excel);
            if(!file.exists())
                file.createNewFile();
            FileOutputStream outputStream = new FileOutputStream( file );
            workbook.write(outputStream);
            outputStream.flush();
            //操作结束，关闭文件
            outputStream.close();
            workbook.close();

            return  file;
        } catch (IOException e) {
            throw BusinessException.build( e.getMessage() );
            //e.printStackTrace();
        }

    }

    /*
     * 列头单元格样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)11);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        return style;

    }

    /*
     * 列数据信息单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }
}
